﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace _5E.Utils.DbHelper
{
    public class BaseDb<TConnection, TCommand, TParameter, TDataAdapter>
        : IDb<DbConnection, DbCommand, DbParameter, DbDataAdapter>
        where TConnection : DbConnection, new()
        where TCommand : DbCommand, new()
        where TParameter : DbParameter, new()
        where TDataAdapter : DbDataAdapter, new()
    {
        #region 变量

        private string m_ConnectionString = null;

        private TConnection m_Connection = null;

        #endregion

        #region 构造函数

        private BaseDb() { }

        public BaseDb(string connectionStr)
            : this()
        {
            this.m_ConnectionString = connectionStr;
        }

        #endregion

        #region ExecuteNonQuery

        public Int32 ExecuteNonQuery(string sql)
        {
            return this.ExecuteNonQuery(this.m_ConnectionString, sql);
        }

        public Int32 ExecuteNonQuery(string sql, params DbParameter[] arrParameter)
        {
            return this.ExecuteNonQuery(this.m_ConnectionString, sql, arrParameter);
        }

        public Int32 ExecuteNonQuery(string sql, CommandType cmdType, params DbParameter[] arrParameter)
        {
            return this.ExecuteNonQuery(this.m_ConnectionString, sql, cmdType, arrParameter);
        }

        public Int32 ExecuteNonQuery(string connectionStr, string sql)
        {
            return this.ExecuteNonQuery(connectionStr, sql, new DbParameter[] { });
        }

        public Int32 ExecuteNonQuery(string connectionStr, string sql, params DbParameter[] arrParameter)
        {
            return this.ExecuteNonQuery(connectionStr, sql, CommandType.Text, arrParameter);
        }

        public Int32 ExecuteNonQuery(string connectionStr, string sql, CommandType cmdType, params DbParameter[] arrParameter)
        {
            return this.Execute<Int32>(connectionStr, sql, cmdType, arrParameter, cmd => cmd.ExecuteNonQuery());
        }

        #endregion

        #region ExecuteBatchNonQuery

        public void ExecuteBatchNonQuery(Dictionary<string, List<DbParameter[]>> dicBatch)
        {
            this.ExecuteBatchNonQuery(this.m_ConnectionString, dicBatch);
        }

        public void ExecuteBatchNonQuery(string connectionStr, Dictionary<string, List<DbParameter[]>> batch)
        {
            this.CheckConnectionString(connectionStr);
            var connection = this.GetConnection(connectionStr);
            using (var trans = connection.BeginTransaction())
            {
                using (var cmd = new TCommand())
                {
                    this.PrepareBatchCommand(cmd, connection, trans);
                    this.ExecuteDifferentSQLNonQuery(cmd, batch, trans);
                }
            }
        }

        private void PrepareBatchCommand(DbCommand cmd, DbConnection connection, DbTransaction trans)
        {
            cmd.Connection = connection;
            cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
        }

        private void ExecuteDifferentSQLNonQuery(TCommand cmd, Dictionary<string, List<DbParameter[]>> batch, DbTransaction trans)
        {
            try
            {
                foreach (var pairBatch in batch)
                {
                    cmd.CommandText = pairBatch.Key;
                    this.ExecuteDifferentDbParameterNonQuery(cmd, pairBatch.Value);
                }
                trans.Commit();
            }
            catch (DbException ex)
            {
                trans.Rollback();
                throw ex;
            }
        }

        private void ExecuteDifferentDbParameterNonQuery(TCommand cmd, List<DbParameter[]> parameterArrayList)
        {
            foreach (var paramValues in parameterArrayList)
            {
                cmd.Parameters.AddRange(paramValues);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

        #endregion

        #region ExecuteScalar

        public object ExecuteScalar(string sql)
        {
            return this.ExecuteScalar(this.m_ConnectionString, sql);
        }

        public object ExecuteScalar(string sql, DbParameter[] arrParameter)
        {
            return this.ExecuteScalar(this.m_ConnectionString, sql, arrParameter);
        }

        public object ExecuteScalar(string sql, CommandType cmdType, DbParameter[] arrParameter)
        {
            return this.ExecuteScalar(this.m_ConnectionString, sql, cmdType, arrParameter);
        }

        public object ExecuteScalar(string connectionStr, string sql)
        {
            return this.ExecuteScalar(connectionStr, sql, new DbParameter[] { });
        }

        public object ExecuteScalar(string connectionStr, string sql, DbParameter[] arrParameter)
        {
            return this.ExecuteScalar(connectionStr, sql, CommandType.Text, arrParameter);
        }

        public object ExecuteScalar(string connectionStr, string sql, CommandType cmdType, DbParameter[] arrParameter)
        {
            return this.Execute(connectionStr, sql, cmdType, arrParameter, cmd => cmd.ExecuteScalar());
        }

        #endregion

        #region ExecuteDataReader

        public DbDataReader GetDataReader(string sql)
        {
            return this.GetDataReader(this.m_ConnectionString, sql);
        }

        public DbDataReader GetDataReader(string sql, DbParameter[] arrParameter)
        {
            return this.GetDataReader(this.m_ConnectionString, sql, arrParameter);
        }

        public DbDataReader GetDataReader(string sql, CommandType cmdType, DbParameter[] arrParameter)
        {
            return this.GetDataReader(this.m_ConnectionString, sql, cmdType, arrParameter);
        }

        public DbDataReader GetDataReader(string connectionStr, string sql)
        {
            return this.GetDataReader(connectionStr, sql, new DbParameter[] { });
        }

        public DbDataReader GetDataReader(string connectionStr, string sql, DbParameter[] arrParameter)
        {
            return this.GetDataReader(connectionStr, sql, CommandType.Text, arrParameter);
        }

        public DbDataReader GetDataReader(string connectionStr, string sql, CommandType cmdType, DbParameter[] arrParameter)
        {
            return this.Execute(connectionStr, sql, cmdType, arrParameter, cmd => cmd.ExecuteReader());
        }

        #endregion

        #region DataTable

        public DataTable GetDataTable(string sql)
        {
            return this.GetDataTable(this.m_ConnectionString, sql);
        }

        public DataTable GetDataTable(string sql, DbParameter[] arrParameter)
        {
            return this.GetDataTable(this.m_ConnectionString, sql, arrParameter);
        }

        public DataTable GetDataTable(string sql, CommandType cmdType, DbParameter[] arrParameter)
        {
            return this.GetDataTable(this.m_ConnectionString, sql, cmdType, arrParameter);
        }

        public DataTable GetDataTable(string connectionStr, string sql)
        {
            return this.GetDataTable(connectionStr, sql, new DbParameter[] { });
        }

        public DataTable GetDataTable(string connectionStr, string sql, DbParameter[] arrParameter)
        {
            return this.GetDataTable(connectionStr, sql, CommandType.Text, arrParameter);
        }

        public DataTable GetDataTable(string connectionStr, string sql, CommandType cmdType, DbParameter[] arrParameter)
        {
            return this.Execute(connectionStr, sql, cmdType, arrParameter, cmd =>
            {
                var dataAdapter = new TDataAdapter();
                dataAdapter.SelectCommand = cmd;
                var table = new DataTable();
                dataAdapter.Fill(table);
                return table;
            });
        }

        #endregion

        #region 其他方法

        public DbConnection GetConnection(string connectionStr)
        {
            if (this.m_Connection == null || this.m_Connection.State == ConnectionState.Closed)
            {
                this.m_Connection = new TConnection();
                this.m_Connection.ConnectionString = connectionStr;
                this.m_Connection.Open();
            }
            else if (this.m_Connection.State == ConnectionState.Broken)
            {
                this.m_Connection.Close();
                this.m_Connection.Open();
            }
            return this.m_Connection;
        }

        private T Execute<T>(string connectionStr, string sql, CommandType cmdType, DbParameter[] arrParameter,
            Func<TCommand, T> executeFunc)
        {
            try
            {
                this.CheckConnectionString(connectionStr);
                this.CheckSql(sql);
                using (TCommand cmd = new TCommand())
                {
                    cmd.Connection = this.GetConnection(connectionStr);
                    cmd.CommandText = sql;
                    cmd.CommandType = cmdType;
                    if (arrParameter != null && 0 < arrParameter.Length)
                    {
                        cmd.Parameters.AddRange(arrParameter);
                    }
                    return executeFunc(cmd);
                }
            }
            catch (NullReferenceException)
            {
                throw;
            }
            catch (DbException)
            {
                throw;
            }
        }

        private void CheckConnectionString(string connectionStr)
        {
            if (string.IsNullOrEmpty(connectionStr))
            {
                throw new NullReferenceException("未设置数据库连接字符串!");
            }
        }

        private void CheckSql(string sql)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new NullReferenceException("SQL语句不可为空!");
            }
        }

        #endregion
    }
}
